package fileDemo.excel;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.CommentsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URI;
import java.util.LinkedList;
import java.util.Queue;

import javax.xml.parsers.ParserConfigurationException;

public abstract class ExcelReader extends DefaultHandler {
    private static final POILogger logger = POILogFactory.getLogger(XSSFSheetXMLHandler.class);
    private StylesTable stylesTable;
    private CommentsTable commentsTable;
    private ReadOnlySharedStringsTableExt sharedStringsTable;
    private boolean vIsOpen;
    private boolean fIsOpen;
    private boolean isIsOpen;
    private boolean hfIsOpen;
    private xssfDataType nextDataType;
    private short formatIndex;
    private String formatString;
    private DataFormatter formatter;
    private int rowNum;
    private int nextRowNum;
    private String cellRef;
    private boolean formulasNotResults;
    private StringBuffer value;
    private StringBuffer formula;
    private StringBuffer headerFooter;
    private Queue<CellAddress> commentCellRefs;
    private int mColumnSize;
    private int mRowSize;
    private String[] mRowData;
    private String mSheetId;

    public int getGetRowDataCount() {
        return getRowDataCount;
    }

    private int getRowDataCount;

    public ExcelReader() {
        this.value = new StringBuffer();
        this.formula = new StringBuffer();
        this.headerFooter = new StringBuffer();
        this.nextDataType = xssfDataType.NUMBER;
        this.formatter = new DataFormatter();
        this.formulasNotResults = false;
        this.commentsTable = null;
        this.nextDataType = xssfDataType.NUMBER;
    }

    public abstract void getRowData(int rowSize, String sheetIndex, int rowIndex, String[] rowList);
    public abstract void onResult(int rowSize, String SheetIndex);

    private void init() {
        if (this.commentsTable != null) {
            this.commentCellRefs = new LinkedList();
            CTComment[] arr$ = this.commentsTable.getCTComments().getCommentList().getCommentArray();
            int len$ = arr$.length;

            for (int i$ = 0; i$ < len$; ++i$) {
                CTComment comment = arr$[i$];
                this.commentCellRefs.add(new CellAddress(comment.getRef()));
            }
        }

    }

    private boolean isTextTag(String name) {
        if ("v".equals(name)) {
            return true;
        } else if ("inlineStr".equals(name)) {
            return true;
        } else {
            return "t".equals(name) && this.isIsOpen;
        }
    }

    @Override
    public void endDocument() throws SAXException {
        super.endDocument();
        onResult(mRowSize, mSheetId);

    }

    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if (uri == null || uri.equals("http://schemas.openxmlformats.org/spreadsheetml/2006/main")) {
            if (qName.equals("dimension")) {
                String dimension = attributes.getValue("ref");
                String endCellIndex = dimension.substring(dimension.indexOf(":") + 1);
                mColumnSize = getColumnSizeFromDimension(endCellIndex);
                mRowSize = getRowSizeFromDimension(endCellIndex);

            }
            if (this.isTextTag(localName)) {
                this.vIsOpen = true;
                this.value.setLength(0);
            } else if ("is".equals(localName)) {
                this.isIsOpen = true;
            } else {
                String cellType;
                String cellStyleStr;
                if ("f".equals(localName)) {
                    this.formula.setLength(0);
                    if (this.nextDataType == xssfDataType.NUMBER) {
                        this.nextDataType = xssfDataType.FORMULA;
                    }

                    cellType = attributes.getValue("t");
                    if (cellType != null && cellType.equals("shared")) {
                        cellStyleStr = attributes.getValue("ref");
                        String si = attributes.getValue("si");
                        if (cellStyleStr != null) {
                            this.fIsOpen = true;
                        } else if (this.formulasNotResults) {
                            logger.log(5, new Object[]{"shared formulas not yet supported!"});
                        }
                    } else {
                        this.fIsOpen = true;
                    }
                } else if (!"oddHeader".equals(localName) && !"evenHeader".equals(localName) && !"firstHeader".equals(localName) && !"firstFooter".equals(localName) && !"oddFooter".equals(localName) && !"evenFooter".equals(localName)) {
                    if ("row".equals(localName)) {
                        cellType = attributes.getValue("r");
                        if (cellType != null) {
                            this.rowNum = Integer.parseInt(cellType) - 1;
                        } else {
                            this.rowNum = this.nextRowNum;
                        }

                        startRow(this.rowNum);
                    } else if ("c".equals(localName)) {
                        this.nextDataType = xssfDataType.NUMBER;
                        this.formatIndex = -1;
                        this.formatString = null;
                        this.cellRef = attributes.getValue("r");
                        cellType = attributes.getValue("t");
                        cellStyleStr = attributes.getValue("s");
                        if ("b".equals(cellType)) {
                            this.nextDataType = xssfDataType.BOOLEAN;
                        } else if ("e".equals(cellType)) {
                            this.nextDataType = xssfDataType.ERROR;
                        } else if ("inlineStr".equals(cellType)) {
                            this.nextDataType = xssfDataType.INLINE_STRING;
                        } else if ("s".equals(cellType)) {
                            this.nextDataType = xssfDataType.SST_STRING;
                        } else if ("str".equals(cellType)) {
                            this.nextDataType = xssfDataType.FORMULA;
                        } else {
                            XSSFCellStyle style = null;
                            if (this.stylesTable != null) {
                                if (cellStyleStr != null) {
                                    int styleIndex = Integer.parseInt(cellStyleStr);
                                    style = this.stylesTable.getStyleAt(styleIndex);
                                } else if (this.stylesTable.getNumCellStyles() > 0) {
                                    style = this.stylesTable.getStyleAt(0);
                                }
                            }

                            if (style != null) {
                                this.formatIndex = style.getDataFormat();
                                this.formatString = style.getDataFormatString();
                                if (this.formatString == null) {
                                    this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
                                }
                            }
                        }
                    }
                } else {
                    this.hfIsOpen = true;
                    this.headerFooter.setLength(0);
                }
            }

        }
    }

    public void endElement(String uri, String localName, String qName) throws SAXException {
        if (uri == null || uri.equals("http://schemas.openxmlformats.org/spreadsheetml/2006/main")) {
            String thisStr = null;
            if (this.isTextTag(localName)) {
                this.vIsOpen = false;
                switch (this.nextDataType) {
                    case BOOLEAN:
                        char first = this.value.charAt(0);
                        thisStr = first == '0' ? "FALSE" : "TRUE";
                        break;
                    case ERROR:
                        thisStr = "ERROR:" + this.value;
                        break;
                    case FORMULA:
                        if (this.formulasNotResults) {
                            thisStr = this.formula.toString();
                        } else {
                            String fv = this.value.toString();
                            if (this.formatString != null) {
                                try {
                                    double d = Double.parseDouble(fv);
                                    thisStr = this.formatter.formatRawCellContents(d, this.formatIndex, this.formatString);
                                } catch (NumberFormatException var11) {
                                    thisStr = fv;
                                }
                            } else {
                                thisStr = fv;
                            }
                        }
                        break;
                    case INLINE_STRING:
                        XSSFRichTextString rtsi = new XSSFRichTextString(this.value.toString());
                        thisStr = rtsi.toString();
                        break;
                    case SST_STRING:
                        String sstIndex = this.value.toString();

                        try {
                            int idx = Integer.parseInt(sstIndex);
                            XSSFRichTextString rtss = new XSSFRichTextString(this.sharedStringsTable.getEntryAt(idx));
                            thisStr = rtss.toString();
                            String tmpStr = rtss.getString();
                            int haha = 1;
                        } catch (NumberFormatException var10) {
                            logger.log(7, new Object[]{"Failed to parse SST index '" + sstIndex, var10});
                        }
                        break;
                    case NUMBER:
                        String n = this.value.toString();
                        if (this.formatString != null && n.length() > 0) {
                            thisStr = this.formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
                            break;
                        }

                        thisStr = n;
                        break;
                    default:
                        thisStr = "(TODO: Unexpected type: " + this.nextDataType + ")";
                }

                this.checkForEmptyCellComments(EmptyCellCommentsCheckType.CELL);
                XSSFComment comment = this.commentsTable != null ? this.commentsTable.findCellComment(new CellAddress(this.cellRef)) : null;
                cell(this.cellRef, thisStr, comment);
            } else if ("f".equals(localName)) {
                this.fIsOpen = false;
            } else if ("is".equals(localName)) {
                this.isIsOpen = false;
            } else if ("row".equals(localName)) {
                this.checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_ROW);
                endRow(this.rowNum);
                this.nextRowNum = this.rowNum + 1;
            } else if ("sheetData".equals(localName)) {
                this.checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_SHEET_DATA);
            } else if (!"oddHeader".equals(localName) && !"evenHeader".equals(localName) && !"firstHeader".equals(localName)) {
                if ("oddFooter".equals(localName) || "evenFooter".equals(localName) || "firstFooter".equals(localName)) {
                    this.hfIsOpen = false;
                    headerFooter(this.headerFooter.toString(), false, localName);
                }
            } else {
                this.hfIsOpen = false;
                headerFooter(this.headerFooter.toString(), true, localName);
            }

        }
    }

    public void characters(char[] ch, int start, int length) throws SAXException {
        if (this.vIsOpen) {
            this.value.append(ch, start, length);
        }

        if (this.fIsOpen) {
            this.formula.append(ch, start, length);
        }

        if (this.hfIsOpen) {
            this.headerFooter.append(ch, start, length);
        }

    }

    private void checkForEmptyCellComments(EmptyCellCommentsCheckType type) {
        if (this.commentCellRefs != null && !this.commentCellRefs.isEmpty()) {
            if (type == EmptyCellCommentsCheckType.END_OF_SHEET_DATA) {
                while (!this.commentCellRefs.isEmpty()) {
                    this.outputEmptyCellComment((CellAddress) this.commentCellRefs.remove());
                }

                return;
            }

            if (this.cellRef == null) {
                if (type == EmptyCellCommentsCheckType.END_OF_ROW) {
                    while (!this.commentCellRefs.isEmpty()) {
                        if (((CellAddress) this.commentCellRefs.peek()).getRow() != this.rowNum) {
                            return;
                        }

                        this.outputEmptyCellComment((CellAddress) this.commentCellRefs.remove());
                    }

                    return;
                }

                throw new IllegalStateException("Cell ref should be null only if there are only empty cells in the row; rowNum: " + this.rowNum);
            }

            CellAddress nextCommentCellRef;
            do {
                CellAddress cellRef = new CellAddress(this.cellRef);
                CellAddress peekCellRef = (CellAddress) this.commentCellRefs.peek();
                if (type == EmptyCellCommentsCheckType.CELL && cellRef.equals(peekCellRef)) {
                    this.commentCellRefs.remove();
                    return;
                }

                int comparison = peekCellRef.compareTo(cellRef);
                if (comparison > 0 && type == EmptyCellCommentsCheckType.END_OF_ROW && peekCellRef.getRow() <= this.rowNum) {
                    nextCommentCellRef = (CellAddress) this.commentCellRefs.remove();
                    this.outputEmptyCellComment(nextCommentCellRef);
                } else if (comparison < 0 && type == EmptyCellCommentsCheckType.CELL && peekCellRef.getRow() <= this.rowNum) {
                    nextCommentCellRef = (CellAddress) this.commentCellRefs.remove();
                    this.outputEmptyCellComment(nextCommentCellRef);
                } else {
                    nextCommentCellRef = null;
                }
            } while (nextCommentCellRef != null && !this.commentCellRefs.isEmpty());
        }

    }

    private void outputEmptyCellComment(CellAddress cellRef) {
        XSSFComment comment = this.commentsTable.findCellComment(cellRef);
        cell(cellRef.formatAsString(), (String) null, comment);
    }

    private static enum EmptyCellCommentsCheckType {
        CELL,
        END_OF_ROW,
        END_OF_SHEET_DATA;

        private EmptyCellCommentsCheckType() {
        }
    }

    static enum xssfDataType {
        BOOLEAN,
        ERROR,
        FORMULA,
        INLINE_STRING,
        SST_STRING,
        NUMBER;

        private xssfDataType() {
        }
    }


    void startRow(int var1) {
        mRowData = new String[mColumnSize];
    }

    void endRow(int var1) {
        getRowDataCount++;
        getRowData(mRowSize, mSheetId, var1, mRowData);
    }

    void cell(String var1, String var2, XSSFComment var3){
        try {
            int curCol = convertColIndexToInt(var1);
            if (curCol>=0 && curCol <= mColumnSize) {
                mRowData[curCol - 1] = var2;
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    void headerFooter(String var1, boolean var2, String var3) {
    }

    public void processOneSheet(String excelPath) throws Exception {
        FileInputStream fs = new FileInputStream(new File(excelPath));//fix android 5.0 不然权限有问题
        OPCPackage pkg = OPCPackage.open(fs);
        ReadOnlySharedStringsTableExt strings = new ReadOnlySharedStringsTableExt(pkg);
//        ArrayList<PackagePart> parts = pkg.getPartsByContentType(XSSFRelation.SHARED_STRINGS.getContentType());
//        SharedStringsTable strings = new SharedStringsTable((PackagePart)parts.get(0));
        XSSFReaderExt xssfReader = new XSSFReaderExt(pkg);
        StylesTable styles = xssfReader.getStylesTable();
        mSheetId = null;
        PackageRelationshipCollection temp = xssfReader.workbookPart.getRelationships();
        for (int i = 0; i < temp.size(); i++) {
            PackageRelationship prs = temp.getRelationship(i);
            URI uri = prs.getTargetURI();
            String path = uri.getPath();
            if (path.endsWith("sheet1.xml")) {
                mSheetId  = prs.getId();
                break;
            }
        }
        if (mSheetId == null || mSheetId.isEmpty()) {
            throw new Exception("parser xml error");
        }
        InputStream sheeetIs = xssfReader.getSheet(mSheetId);
        InputSource sheetSource = new InputSource(sheeetIs);
        try {
            XMLReader sheetParser = SAXHelper.newXMLReader();
            this.stylesTable = styles;
            this.sharedStringsTable = strings;
            this.init();
            sheetParser.setContentHandler(this);
            getRowDataCount = 0;
            sheetParser.parse(sheetSource);
            sheeetIs.close();
            pkg.close();
        } catch (ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
        }
    }

    public static int getColumnSizeFromDimension(String rowId) {
        int firstDigit = -1;
        for (int c = 0; c < rowId.length(); ++c) {
            if (Character.isDigit(rowId.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        //AB7-->AB
        //AB是列号, 7是行号
        String newRowId = rowId.substring(0, firstDigit);
        int num = 0;
        int result = 0;
        int length = newRowId.length();
        for (int i = 0; i < length; i++) {
            //先取最低位，B
            char ch = newRowId.charAt(length - i - 1);
            //B表示的十进制2，ascii码相减，以A的ascii码为基准，A表示1，B表示2
            num = (int) (ch - 'A' + 1);
            //列号转换相当于26进制数转10进制
            num *= Math.pow(26, i);
            result += num;
        }
        return result;
    }

    public static int getRowSizeFromDimension (String rowId) {
        int firstDigit = -1;
        for (int c = 0; c < rowId.length(); ++c) {
            if (Character.isDigit(rowId.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        //AB7-->AB
        //AB是列号, 7是行号
        String number = rowId.substring(firstDigit, rowId.length());
        return Integer.parseInt(number);
    }

    private int convertColIndexToInt(String value) {
        String col = value.replaceAll("\\d+", "").toUpperCase();
        col = fillChar(col, 3, '@', true);
        char[] colLetter = col.toCharArray();

        int ret = 0;
        if (colLetter[0] != '@') {
            ret += (colLetter[0] -'A' + 1) * 26 * 26;
        }
        if (colLetter[1] != '@') {
            ret += (colLetter[1] -'A' + 1) * 26;
        }
        if (colLetter[2] != '@') {
            ret += colLetter[2] -'A' + 1;
        }

        return ret;
    }

    String fillChar(String str, int len, char let, boolean isPre){
        int len_1 = str.length();
        if(len_1 <len){
            if(isPre){
                for(int i=0;i<(len-len_1);i++){
                    str = let+str;
                }
            }else{
                for(int i=0;i<(len-len_1);i++){
                    str = str+let;
                }
            }
        }
        return str;
    }
}
